use [testdb]

EXEC tSQLt.NewTestClass 'MyTests'
GO

CREATE PROCEDURE MyTests.[test Consulta1]
AS
BEGIN
	IF OBJECT_ID('actual') IS NOT NULL DROP TABLE actual;
	IF OBJECT_ID('expected') IS NOT NULL DROP TABLE expected;
    
	CREATE TABLE actual (
		cod_tipo_objeto  varchar(3),
		tipo_objeto      varchar(30),
		categoria        varchar(30),
		id_objeto        varchar(30),
		inf_objeto		 varchar(1000),
		cod_impuesto     varchar(10),
		impuesto         varchar(30),
		deuda			 varchar(30)
	);
	
	DECLARE @StartTime datetime,@EndTime datetime, @Duration datetime
	SELECT  @StartTime=GETDATE()

	insert into actual exec dbo.proc_consulta1;

	SELECT @EndTime=GETDATE()
	SELECT @Duration = DATEDIFF(s,@StartTime,@EndTime)
	
	IF( @Duration > 1 )
		Exec tSQLt.Fail 'Time Exceded'
		
	CREATE TABLE expected (
		cod_tipo_objeto  varchar(3),
		tipo_objeto      varchar(30),
		categoria        varchar(30),
		id_objeto        varchar(30),
		inf_objeto		 varchar(1000),
		cod_impuesto     varchar(10),
		impuesto         varchar(30),
		deuda			 varchar(30)
	);
	
	insert into expected
	select * from Consulta1
    
	EXEC tSQLt.AssertEqualsTable 'expected', 'actual';
END;
GO


CREATE PROCEDURE MyTests.[test Consulta2]
AS
BEGIN
    IF OBJECT_ID('actual') IS NOT NULL DROP TABLE actual;
    IF OBJECT_ID('expected') IS NOT NULL DROP TABLE expected;
    
    CREATE TABLE actual (
		categoria        varchar(30),
		cod_impuesto     varchar(10),
		impuesto         varchar(30),
		deuda			 varchar(30)
    );

	DECLARE @StartTime datetime,@EndTime datetime, @Duration datetime
	SELECT  @StartTime=GETDATE()

	insert into actual
	exec proc_consulta2
	
	SELECT @EndTime=GETDATE()
	SELECT @Duration = DATEDIFF(s,@StartTime,@EndTime)
	
	IF( @Duration > 1 )
		Exec tSQLt.Fail 'Time Exceded'

	
	CREATE TABLE expected (
		categoria        varchar(30),
		cod_impuesto     varchar(10),
		impuesto         varchar(30),
		deuda			 varchar(30)
    );
    
    declare @id_contribuyente integer,
        @cod_tipo_objeto  varchar(3),
        @id_objeto		  varchar(30)

	insert into expected select * from Consulta2
 
 EXEC tSQLt.AssertEqualsTable 'expected', 'actual';
END;
GO

create PROCEDURE MyTests.[test Consulta3]

AS
BEGIN
	declare @rec_ideal		decimal(15,2),
			@rec_prevista	decimal(15,2)
	
	
	DECLARE @StartTime datetime,@EndTime datetime, @Duration datetime
	SELECT  @StartTime=GETDATE()

	exec dbo.proc_consulta3
	@rec_ideal output,
	@rec_prevista output
	
	SELECT @EndTime=GETDATE()
	SELECT @Duration = DATEDIFF(s,@StartTime,@EndTime)
	
	IF( @Duration > 5 )
		Exec tSQLt.Fail 'Time Exceded'	
	
    DECLARE @expected_recaudacion_ideal decimal(15,2); set @expected_recaudacion_ideal = 2600068000.00;
    DECLARE @expected_recaudacion_prevista decimal(15,2); SET @expected_recaudacion_prevista = 1300034000.00;
    
    EXEC tSQLt.AssertEquals @expected_recaudacion_ideal, @rec_ideal;
    EXEC tSQLt.AssertEquals @expected_recaudacion_prevista, @rec_prevista;
END
GO

create PROCEDURE MyTests.[test Consulta4]
AS
BEGIN
declare	@total			decimal(15,2),
			@prevista		decimal(15,2),
			@real			decimal(15,2),
			@porc_previsto	decimal(5,2),
			@porc_real		decimal(5,2)

	DECLARE @StartTime datetime,@EndTime datetime, @Duration datetime
	SELECT  @StartTime=GETDATE()

	exec dbo.proc_consulta4
		@total			output,
		@prevista		output,
		@real			output,
		@porc_previsto	output,
		@porc_real		output
	
	SELECT @EndTime=  GETDATE()
	SELECT @Duration = DATEDIFF(s,@StartTime,@EndTime)
	
	IF( @Duration > 10 )
		Exec tSQLt.Fail 'Time Exceded'	
		
    DECLARE @expected_porc_previsto	decimal(5,2); set @expected_porc_previsto = 50.00;
	DECLARE	@expected_porc_real		decimal(5,2); set @expected_porc_real = 61.14;
	DECLARE	@expected_total			decimal(15,2); SET @expected_total = 2600068000.00;
	DECLARE	@expected_prevista		decimal(15,2); SET @expected_prevista = 1300034000.00;
	DECLARE	@expected_real			decimal(15,2); SET @expected_real = 1589718677.37;
      
    EXEC tSQLt.AssertEquals @expected_porc_previsto, @porc_previsto;
    EXEC tSQLt.AssertEquals @expected_porc_real, @porc_real;
    EXEC tSQLt.AssertEquals @expected_total, @total;
	EXEC tSQLt.AssertEquals @expected_prevista, @prevista;
	EXEC tSQLt.AssertEquals @expected_real, @real;    
    
END
GO

create PROCEDURE MyTests.[test Consulta5]
AS
BEGIN
	 IF OBJECT_ID('actual') IS NOT NULL DROP TABLE actual;
    IF OBJECT_ID('expected') IS NOT NULL DROP TABLE expected;
    
    CREATE TABLE actual (
		fecha date,
		recaudacion decimal(20,2)
	);

DECLARE @StartTime datetime,@EndTime datetime, @Duration datetime
	SELECT  @StartTime=GETDATE()

	insert into actual exec dbo.proc_consulta5

	SELECT @EndTime=GETDATE()
	SELECT @Duration = DATEDIFF(s,@StartTime,@EndTime)
	
	IF( @Duration > 10 )
		Exec tSQLt.Fail 'Time Exceded'	
		
	CREATE TABLE expected (
		fecha date,
		recaudacion decimal(20,2)
	);

    insert into expected
	select * from Consulta5
	  
    EXEC tSQLt.AssertEqualsTable 'actual', 'expected';
END
GO

